import { Store, Authority, Role, User, Level } from '../interface'
import { MD5 } from '../utils'
import { createPool, ConnectionConfig, Pool } from 'mysql'

export interface MysqlStoreOptions {
    show_sql?: boolean
    authorities: {
        tablename: string
        columns: {
            id: string
            name: string
        }
    }
    roles: {
        tablename: string
        columns: {
            id: string
            name: string
            is_admin: string
        }
    }
    role_auth_rel: {
        tablename: string
        columns: {
            id: string
            role_id: string
            auth_id: string
            level: string
        }
    }
    users: {
        tablename: string
        columns: {
            id: string
            name: string
            nickname: string
            role_id: string
            password: string
            is_sys?: string
            lock_ips?: string
            expires?: string
        }
    }
}
export interface IMysqlStore extends Store{
    new (config: ConnectionConfig, options: MysqlStoreOptions): this
}
export class MysqlStore implements Store {
    createToken(acc: { name: any; password: string; }) {
        return MD5(`${acc.name}:${MD5(acc.password)}`)
    }
    options: MysqlStoreOptions
    config: ConnectionConfig
    usePasswordChange = true
    execute = (sql: string, values?: any[]): Promise<any> => new Promise((resolve, reject) => {
        const { show_sql } = this.options
        this.pool.getConnection(function (err, conn) {
            if (err) {
                console.trace(err)
                reject(err)
            } else {
                const r = conn.query(sql, values, function (err, result) {
                    if (err) {
                        console.trace(err)
                        reject(err)
                    } else {
                        show_sql && console.log(r.sql)
                        resolve(result)
                    }
                    conn.release()
                })
            }
        })
    })

    __obj: {
        authorities: Authority[]
        roles: Role[]
        users: User[]
    } = {
        authorities: [],
        roles: [],
        users: []
    }
    __userMap: Map<string, User> = new Map<string, User>()
    __initUserMap = () => {
        this.__userMap = new Map()
        this.__obj.users.map(user => {
            const key = MD5(`${user.name}:${user.password}`)
            this.__userMap.set(key, {
                ...user,
                token: key,
                role: this.__obj.roles.find(r => r.id === user.roleId)
            })
        })
    }
    pool: Pool
    constructor(config: ConnectionConfig, options: MysqlStoreOptions) {
        this.options = options
        this.config = config
        this.pool = createPool(config)
        this.refresh()
    }

    refresh = async () => {
        const authorities = await this.authorities()
        const roles = await this.roles()
        const users = await this.users()
        this.__obj = { authorities, roles, users }
        this.__initUserMap()
    }
    async authorities () {
        const { tablename, columns } = this.options.authorities
        return this.__obj.authorities = await this.execute(`select ${columns.id} as id, ${columns.name} as name from ${tablename}`)
    }
    async roles () {
        const { roles, role_auth_rel } = this.options
        const { auth_id, level, role_id } = role_auth_rel.columns
        const list: Role[] = await this.execute(`select ${roles.columns.id} as id, ${roles.columns.name} as name, ${roles.columns.is_admin} as isAdmin from ${roles.tablename}`)
        const auths: { id: string, role_id: number, level: number }[] = await this.execute(`select ${auth_id} as id, ${role_id} as role_id, ${level} as level from ${role_auth_rel.tablename}`)
        for (let i = 0; i < list.length; i++) {
            let a = list[i].authorities = {}
            auths.map(({ id, role_id, level }) => {
                if (role_id === list[i].id) {
                    a[id] = level
                }
            })
        }
        return this.__obj.roles = list
    }
    async users () {
        const { users } = this.options
        const columns = [
            `${users.columns.id} as id`,
            `${users.columns.name} as name`,
            `${users.columns.nickname} as nickname`,
            `${users.columns.password} as password`,
            `${users.columns.role_id} as roleId`,
        ]
        if (users.columns.lock_ips) {
            columns.push(`${users.columns.lock_ips} as lock_ips`)
        }
        if (users.columns.expires) {
            columns.push(`${users.columns.expires} as expires`)
        }
        if (users.columns.is_sys) {
            columns.push(`${users.columns.is_sys} as isSys`)
        }

        this.__obj.users = await this.execute(`select ${columns.join(', ')} from ${users.tablename}`)
        this.__initUserMap()
        return this.__obj.users
    }

    async addRole(roleName: string) {
        const { roles } = this.options
        const { insertId } = await this.execute(`insert into ${roles.tablename} set ${roles.columns.name}=?`, [ roleName ])
        return insertId
    }
    async delRole(roleId: string) {
        const { roles } = this.options
        return await this.execute(`delete from ${roles.tablename} where ${roles.columns.id}=?`, [ Number(roleId) ])
    }
    async addUser(userName: string, password: string, nickname?: string) {
        const { users } = this.options
        const c = users.columns
        const res = await this.execute(`insert into ${users.tablename} set ${c.name}=?, ${c.password}=?, ${c.nickname}=?`, [
            userName, MD5(password || userName), nickname
        ])
        this.users()
        return res
    }
    async delUser(userId: string) {
        const { users } = this.options
        await this.execute(`delete from ${users.tablename} where ${users.columns.id}=?`, [Number(userId)])
        this.users()
    }
    async changeUserPassword(userId: string, password?: string) {
        const { users } = this.options
        await this.execute(`update ${users.tablename} set ${users.columns.password}=? where ${users.columns.id}=?`, [MD5(password || userId), Number(userId)])
        this.users()
    }
    async updateRoleAuthority(roleId: string, authorityId: string, level: Level) {
        const { role_auth_rel: rel } = this.options
        const _roleId = Number(roleId) || null
        const _authId = Number(authorityId) || null
        if (level === Level.NONE) {
            return await this.execute(`delete from ${rel.tablename} where ${rel.columns.role_id}=? and ${rel.columns.auth_id}=?`, [_roleId, _authId])
        } else {
            const [item] = await this.execute(`select ${rel.columns.id} as id from ${rel.tablename} where ${rel.columns.role_id}=? and ${rel.columns.auth_id}=?`, [_roleId, _authId])
            if (item) {
                return await this.execute(`update ${rel.tablename} set ${rel.columns.level}=? where ${rel.columns.id}=?`, [level, item.id])
            } else {
                return await this.execute(`insert into ${rel.tablename} set ${rel.columns.role_id}=?, ${rel.columns.auth_id}=?, ${rel.columns.level}=?`, [_roleId, _authId, level])
            }
        }
    }
    async updateUserRole(userId: string, roleId: string) {
        const { users: {
            tablename,
            columns
        } } = this.options
        const _roleId = Number(roleId) || null
        await this.execute(`update ${tablename} set ${columns.role_id}=? where ${columns.id}=?`, [_roleId, Number(userId)])
        this.refresh()
        return 
    }
    async updateUserLockIPs(userId: string, ips: string[]) {
        const { users: {
            tablename,
            columns
        } } = this.options
        await this.execute(`update ${tablename} set ${columns.lock_ips}=? where ${columns.id}=?`, [Number(userId), ips.join(',')])
        this.refresh()
        return
    }
    async updateUserExpires(userId: string, expires: number) {
        const { users: {
            tablename,
            columns
        } } = this.options
        await this.execute(`update ${tablename} set ${columns.expires}=? where ${columns.id}=?`, [Number(userId), expires])
        this.refresh()
        return
    }
    getLoginUser = (key: string) => {
        return this.__userMap.get(key)
    }
    lookLoginUser = async (key: string) => {
        await this.refresh()
        return this.__userMap.get(key)
    }
}